# -*- coding: UTF-8 -*-
from appraisals.consql import conn
# from jixiao.connetsql import conn
import pandas as pd
import time
from zwyczc import df_sum,phone_sum
from main import year,month




if __name__ =='__main__':
    start = time.time()
    # sql_zwyczc ="select create_user,TableID,CreateTime from zwyczc where year(CreateTime)="+year+" and month(CreateTime)="+month+";" #装维远程支撑统计
    sql_sbgz = "select * from shebeiguzhangluru where year(CreateTime)="+year+" and month(CreateTime)="+month+";" #设备故障统计
    sql_jhcc = "select * from KHZC_jhchacuo where year(CreateTime)="+year+" and month(CreateTime)="+month+";" #激活差错统计
    sql_police ="select * from police where year(CreateTime)="+year+" and month(CreateTime)="+month+";" #公安查询统计
    sql_zongjinglirexian ="select * from zongjinglirexian where year(CreateTime)="+year+" and month(CreateTime)="+month+";" #总经理热线统计
    sql_IPV6 ="select * from IPV6 where year(CreateTime)="+year+" and month(CreateTime)="+month+";" #IPV6统计
    sql_ywdj = "select * from ywdj where year(CreateTime)="+year+" and month(CreateTime)="+month+";" #易问登记统计
    sql_KHZC_gmgn ="select * from KHZC_gmgn where year(CreateTime)="+year+" and month(CreateTime)="+month+";" #公免登记统计
    #装维远程支撑统计
    # df = pd.read_sql(sql_zwyczc,con=conn)
    # df2 =df.groupby('create_user',as_index=False)['TableID'].count()
    # df2.columns=['记录人','电话支撑量']
    # df2 = df2.sort_values(by=['记录人'])
    #设备故障统计
    df = pd.read_sql(sql_sbgz,con=conn)
    df3 =df.groupby('c_user',as_index=False)['TableID'].count()
    df3.columns=['记录人','设备故障数量']
    df2 = pd.merge(df_sum,df3,how='outer').fillna(value=0)
    #激活差错统计
    df =pd.read_sql(sql_jhcc,con=conn)
    df3 =df.groupby('feed_user',as_index=False)['TableID'].count()
    df3.columns=['记录人','激活差错数量']
    df2 = pd.merge(df2,df3,how='outer').fillna(value=0)
     #公安查询统计
    df =pd.read_sql(sql_police,con=conn)
    df3 =df.groupby('jlr',as_index=False)['TableID'].count()
    df3.columns=['记录人','公安登记数量']
    df2 = pd.merge(df2,df3,how='outer').fillna(value=0)
     #公安查询统计
    df =pd.read_sql(sql_zongjinglirexian,con=conn)
    df3 =df.groupby('jyr',as_index=False)['TableID'].count()
    df3.columns=['记录人','总经理登记数量']
    df2 = pd.merge(df2,df3,how='outer').fillna(value=0)
    df3 =df.groupby('clr',as_index=False)['TableID'].count()
    df3.columns=['记录人','总经理处理数量']
    df2 = pd.merge(df2,df3,how='outer').fillna(value=0)
    #IPV6统计
    df =pd.read_sql(sql_IPV6,con=conn)
    df3 =df.groupby('create_user',as_index=False)['TableID'].count()
    df3.columns=['记录人','IPV6数量']
    df2 = pd.merge(df2,df3,how='outer').fillna(value=0)
    #易问登记统计
    df =pd.read_sql(sql_ywdj,con=conn)
    df3 =df.loc[df['CLFS'] == '办结'].groupby('CJZ',as_index=False)['TableID'].count()
    df3.columns=['记录人','易问办结数量']
    df2 = pd.merge(df2,df3,how='outer').fillna(value=0)

    df3 =df.loc[df['CLFS'] == '转派'].groupby('CJZ',as_index=False)['TableID'].count()
    df3.columns=['记录人','易问转派数量']
    df2 = pd.merge(df2,df3,how='outer').fillna(value=0)

    df3 =df.loc[df['CLFS'] == '退单'].groupby('CJZ',as_index=False)['TableID'].count()
    df3.columns=['记录人','易问退单数量']
    df2 = pd.merge(df2,df3,how='outer').fillna(value=0)
    #公免登记统计
    df =pd.read_sql(sql_KHZC_gmgn,con=conn)
    df3 =df.groupby('add_user',as_index=False)['TableID'].count()
    df3.columns=['记录人','公免数量']
    df2 = pd.merge(df2,df3,how='outer').fillna(value=0)
    df2 = df2.sort_values(by=['记录人'])


    df2.loc[df2.shape[0]] =['合计',df2['电话总量'].sum(),df2['登记不规范量'].sum(),df2['跟踪数'].sum(),df2['重复量'].sum(),phone_sum,df2['设备故障数量'].sum(),df2['激活差错数量'].sum(),df2['公安登记数量'].sum(),df2['总经理登记数量'].sum(),df2['总经理处理数量'].sum(),df2['IPV6数量'].sum(),df2['易问办结数量'].sum(),df2['易问转派数量'].sum(),df2['易问退单数量'].sum(),df2['公免数量'].sum()]
    # df2['合计数量']=df2.ix[0:,[x for x in range(1,df2.shape[1])]].apply(lambda x: x.sum(), axis=1)  #对列求和
    # print(df2)
    excelname=month+"月份"
    df2.to_excel(excelname+'.xlsx',index=None)
    print('合计用时:{}'.format(time.time()-start))



